#!/bin/bash

if [ -n "$1" ]; then
    data_date=$1
else
  data_date=`date -d '-1 days' +%F`
fi
ads_new_users_day_number="
insert overwrite table vivo_app_warehouse.ads_new_users_day_number
select * from (
                  WITH t1 AS (
                      SELECT
                          log_id,
                          user_id,
                          user_register_date
                      FROM vivo_app_warehouse.dwd_user_action_log
                      WHERE dt = '${data_date}'
                  )
                  SELECT
                      t1.user_register_date as dt,
                      COUNT(DISTINCT t1.user_id) AS new_users
                  FROM t1
                  GROUP BY t1.user_register_date
              );
"
ads_daily_active_users_number="
insert overwrite table vivo_app_warehouse.ads_daily_active_users_number
SELECT
    dt,
    COUNT(DISTINCT user_id) AS dau
FROM vivo_app_warehouse.dwd_user_action_log
GROUP BY dt;
"
ads_weekly_active_users_number="
insert overwrite table vivo_app_warehouse.ads_weekly_active_users_number
SELECT
    WEEKOFYEAR(event_time) AS week_start_date,
    COUNT(DISTINCT user_id) AS wau
FROM vivo_app_warehouse.dwd_user_action_log
GROUP BY week_start_date;
"
ads_month_active_users_number="
insert overwrite table vivo_app_warehouse.ads_month_active_users_number
SELECT
    MONTH(event_time) AS month,
    COUNT(DISTINCT user_id) AS mau
FROM vivo_app_warehouse.dwd_user_action_log
GROUP BY month;
"
ads_user_use_time_day="
INSERT OVERWRITE TABLE vivo_app_warehouse.ads_user_use_time_day
SELECT
    dt
    ,avg(login_time) AS avg_usage_duration_seconds
    ,avg(login_time)/60 AS avg_usage_duration_minutes
FROM (
         SELECT
             dt, user_id, event
              ,CASE
                   WHEN event = 'logout' THEN 0
                   WHEN event = 'login' THEN 24*60*60
             END AS login_time
         FROM (
                  SELECT
                      date_format(event_time,'yyyy-MM-dd') AS dt
                       ,user_id
                       ,concat_ws(',',collect_set(event_type)) AS event
                  FROM vivo_app_warehouse.dwd_user_action_log
                  WHERE event_type IN ('login','logout')
                  GROUP BY date_format(event_time,'yyyy-MM-dd'),user_id
              )t1
     )t2
GROUP BY dt;
"
ads_user_active_time_day="
INSERT OVERWRITE TABLE vivo_app_warehouse.ads_user_active_time_day
SELECT
    hour_of_day
    ,active_users
    ,if(active_users=0,0,active_users/sum_active_users) AS percentage
FROM (
         SELECT
             date_format(event_time,'HH') AS hour_of_day
              ,sum(if(user_is_active='true',1,0)) AS active_users
              ,sum(sum(if(user_is_active='true',1,0))) OVER(PARTITION BY date_format(event_time,'HH')) AS sum_active_users
         FROM vivo_app_warehouse.dwd_user_action_log
         GROUP BY date_format(event_time,'HH')
     )t1;
"
ads_function_use_par="
INSERT OVERWRITE TABLE vivo_app_warehouse.ads_function_use_par
SELECT
    dt
   ,event_type
    ,count(1) AS unique_users
    ,round(count(DISTINCT user_id) * 1.0 / count(1) * 1.0,4) AS user_coverage_rate
FROM vivo_app_warehouse.dwd_user_action_log
WHERE event_type IN ('app_launch','search','settings','payment','share')
GROUP BY event_type,dt;
"
ads_funnel_tb="
INSERT OVERWRITE TABLE vivo_app_warehouse.ads_funnel_tb
SELECT
    total_users, product_viewers, cart_adders, checkout_starters, paying_users
     ,round(if(view_to_cart_rate IS NULL,0,view_to_cart_rate),4) AS view_to_cart_rate
     ,round(if(cart_to_checkout_rate IS NULL,0,cart_to_checkout_rate),4) AS cart_to_checkout_rate
     ,round(if(checkout_to_paid_rate IS NULL,0,checkout_to_paid_rate),4) AS checkout_to_paid_rate
     ,round(if(overall_conversion_rate IS NULL,0,overall_conversion_rate),4) AS overall_conversion_rate
FROM (
         SELECT
             total_users
              ,product_viewers
              ,cart_adders
              ,checkout_starters
              ,paying_users
              ,(cart_adders * 1.0 / product_viewers) AS view_to_cart_rate
              ,(checkout_starters * 1.0 / cart_adders) AS cart_to_checkout_rate
              ,(paying_users * 1.0 / checkout_starters) AS checkout_to_paid_rate
              ,(product_viewers * 1.0 / paying_users) AS overall_conversion_rate
         FROM (
                  SELECT
                      count(DISTINCT user_id) AS total_users
                       ,count(DISTINCT if(event_type = 'product_view',user_id,NULL)) AS product_viewers
                       ,count(DISTINCT if(event_type = 'add_to_cart',user_id,NULL)) AS cart_adders
                       ,count(DISTINCT if(event_type = 'checkout_start',user_id,NULL)) AS checkout_starters
                       ,count(DISTINCT if(event_type = 'payment_success',user_id,NULL)) AS paying_users
                  FROM vivo_app_warehouse.dwd_user_action_log
                  WHERE  event_type IN ('product_view','add_to_cart','checkout_start','payment_success')
              )t1
     )t2;
"
ads_app_download_uninstall="
WITH tmp1 AS (
    SELECT
        app_id
         ,app_name
         ,app_category
         ,count(DISTINCT user_id) AS download_users
         ,count(if(event_type = 'download', 1, 0)) AS download_count
         ,avg(avg_rating) AS  avg_download_rating
    FROM vivo_app_warehouse.dwd_user_action_log
    WHERE dt = '${data_date}'
      AND event_type = 'download'
    GROUP BY app_id,app_name, app_category
    ORDER BY download_count DESC , download_users DESC
), tmp2 AS (
    SELECT
        app_id
         ,app_name
         ,app_category
         ,count(DISTINCT user_id) AS uninstall_users
         ,count(if(event_type = 'stop', 1, 0)) AS uninstall_count
         ,avg(avg_rating) AS  avg_uninstall_rating
    FROM vivo_app_warehouse.dwd_user_action_log
    WHERE dt = '${data_date}'
      AND event_type = 'stop'
    GROUP BY app_id,app_name, app_category
    ORDER BY uninstall_count DESC , uninstall_users DESC
)
INSERT INTO vivo_app_warehouse.ads_app_download_uninstall
SELECT
    a.app_id
     ,a.app_name
     ,a.app_category
     ,a.download_users
     ,a.download_count
     ,a.avg_download_rating
     ,b.uninstall_users
     ,b.uninstall_count
     ,b.avg_uninstall_rating
FROM tmp1 a
         LEFT JOIN tmp2 b ON a.app_id = b.app_id
;
"
ads_app_crash_persponse="
WITH t1 AS (
    SELECT
        app_id
         ,app_name
         ,app_version
         ,count(DISTINCT device_id) AS affected_devices
         ,count(if(event_type = 'dislike',1,0)) AS crash_count
         ,round(COUNT(device_id) / (SELECT COUNT(device_id) FROM vivo_app_warehouse.dwd_user_action_log WHERE event_type = 'dislike'),4) AS percentage
    FROM vivo_app_warehouse.dwd_user_action_log
    WHERE dt = '${data_date}'
      AND event_type = 'dislike'
    GROUP BY app_id,app_name, app_version
), t2 AS (
    SELECT
        app_id
         ,app_name
         ,app_version
         ,app_version
         ,percentile_approx(unix_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss'), 0.5) AS median_response_time
         ,percentile_approx(unix_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss'), 0.9) AS p90_response_time
         ,percentile_approx(unix_timestamp(event_time, 'yyyy-MM-dd HH:mm:ss'), 0.99) AS p99_response_time
    FROM vivo_app_warehouse.dwd_user_action_log
    WHERE dt = '${data_date}'
    GROUP BY app_id,app_name, app_version
)
INSERT INTO  vivo_app_warehouse.ads_app_crash_persponse
SELECT
    a.app_id
     ,a.app_name
     ,a.app_version
     ,a.affected_devices
     ,a.crash_count
     ,a.percentage
     ,b.median_response_time
     ,b.p90_response_time
     ,b.p99_response_time
FROM t1 a
         LEFT JOIN t2 b ON a.app_id = b.app_id
;
"
zb4_1="
INSERT OVERWRITE TABLE vivo_app_warehouse.zb4_1
SELECT
    device_type
     ,count(*) AS device_count
     ,avg(CAST(REPLACE(REPLACE(battery_level, '%', ''), ',', '') AS DECIMAL(10,2))) AS avg_battery_health
     ,PERCENTILE(CAST(REPLACE(REPLACE(battery_level, '%', ''), ',', '') AS DECIMAL(10,2)), 0.1) AS p10_battery_health
     ,PERCENTILE(CAST(REPLACE(REPLACE(battery_level, '%', ''), ',', '') AS DECIMAL(10,2)), 0.5) AS median_battery_health
     ,PERCENTILE(CAST(REPLACE(REPLACE(battery_level, '%', ''), ',', '') AS DECIMAL(10,2)), 0.9) AS p90_battery_health
FROM vivo_app_warehouse.dwd_user_action_log
GROUP BY device_type
ORDER BY device_count DESC;
"
zb4_2="
INSERT OVERWRITE TABLE vivo_app_warehouse.zb4_2
SELECT
    memory_usage,
    AVG(CAST(
            substring(
                    memory_usage,
                    1,
                    length(memory_usage) - length('MB')
                ) AS INT
        )) / SUM(CAST(
            substring(
                    memory_usage,
                    1,
                    length(memory_usage) - length('MB')
                ) AS INT
        )) AS avg_used_percent,
    COUNT(*) AS device_count,
    SUM(IF(battery_level > '90%', 1, 0)) AS high_battery_devices
FROM
    vivo_app_warehouse.dwd_user_action_log
GROUP BY memory_usage;
"
zb5="
INSERT OVERWRITE TABLE vivo_app_warehouse.zb5
SELECT
    dt
     ,sum(if(user_is_active=false,1,null)) AS active_users
     ,sum(if(event_type='play',1,null)) AS paying_count
     ,sum(price) AS total_revenue
     ,avg(price) AS arpu
     ,avg(if(user_is_active=true,price,null)) AS arppu
FROM vivo_app_warehouse.dwd_user_action_log
GROUP BY dt
ORDER BY dt;
"
/opt/module/spark/bin/beeline -u jdbc:hive2://node101:10001 -n bwie -e "
${ads_new_users_day_number}
${ads_daily_active_users_number}
${ads_weekly_active_users_number}
${ads_month_active_users_number}
${ads_user_use_time_day}
${ads_user_active_time_day}
${ads_function_use_par}
${ads_funnel_tb}
${ads_app_download_uninstall}
${ads_app_crash_persponse}
${zb4_1}
${zb4_2}
${zb5}
"